package com.zeng.createmd.restfulmodule.util;

import com.zeng.createmd.servicemodule.vo.DatabaseVo;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

public class JDBCUtil {

    public static Connection getConnection(DatabaseVo databaseVo) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }
        Connection conn = null;
        try {
            String databaseName = "";
            if (StringUtils.isNotEmpty(databaseVo.getName())) {
                databaseName = "/" + databaseVo.getName();
            }
            conn = DriverManager.getConnection("jdbc:mysql://" + databaseVo.getAddress() + ":" + databaseVo.getPort() + databaseName + "?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false", databaseVo.getAccount(), databaseVo.getPassword());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接不上数据库,请查看数据库服务或检查账号密码");
        }
        return conn;
    }

    public static void createDatabase(DatabaseVo databaseVo) throws SQLException {
        if (databaseVo == null) {
            return;
        }
        String databaseName = databaseVo.getName();
        if (databaseName == null) {
            return;
        }
        Connection connection = null;
        Statement statement;
        try {
            databaseVo.setName(null);
            connection = getConnection(databaseVo);
            Set<String> databases = databases(connection);
            connection = getConnection(databaseVo);
            if (databases.contains(databaseName)) {
                throw new RuntimeException("数据库已经存在");
            }
            databaseVo.setName(databaseName);
            statement = connection.createStatement();
            String sql = String.format(" CREATE DATABASE %s /*!40100 DEFAULT CHARACTER SET utf8mb4 */", databaseName);
            statement.execute(sql);
        } finally {
            if (connection != null) {
                connection.close();
            }
        }

    }

    public static void executeSql(Connection connection, String sql) throws SQLException {
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public static Set<String> databases(Connection connection) throws SQLException {
        Set<String> databases;
        try {
            Statement statement = connection.createStatement();
            ResultSet show_databases = statement.executeQuery("show databases");
            databases = new HashSet<>();
            while (show_databases.next()) {
                databases.add(show_databases.getString(1));
            }
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return databases;
    }

    public static boolean existDatabase(DatabaseVo databaseVo) throws SQLException {
        String databaseName = databaseVo.getName();
        databaseVo.setName(null);
        Set<String> databases = databases(getConnection(databaseVo));
        return databases.contains(databaseName);
    }

    public static void main(String[] args) throws SQLException {
        DatabaseVo build = DatabaseVo.builder().address("127.0.0.1").port(3306).account("root").password("root").build();
        build.setName("testzeng");
        createDatabase(build);
    }
}
